package com.idc.excel.cr;

import java.io.File;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.atomic.AtomicInteger;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.LoggerFactory;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;

import ch.qos.logback.classic.Level;
import ch.qos.logback.classic.LoggerContext;

public class ReadFormulaAndDo {

	public static void doWithList(int threadNum, int every, String writePath, ExecutorService exe,
			Map<String, List<CrData>> dataMap, LinkedBlockingQueue<CrData> queue) {
		List<List<CrData>> all = new ArrayList<>();
		List<Future<List<CrData>>> fs = new ArrayList<>();
		for (int i = 0; i < threadNum; i++) {
			List<CrData> tList = new ArrayList<CrData>();
			for (int j = 0; j < every; j++) {
				CrData crData = queue.poll();
				if (null == crData) {
					break;
				}
				tList.add(crData);
			}
			all.add(tList);
		}
		for (int i = 0; i < threadNum; i++) {
			MyCallable myCallable = new MyCallable(all.get(i), writePath);
			Future<List<CrData>> future = exe.submit(myCallable);
			fs.add(future);
		}
		fs.forEach(f -> {
			try {
				List<CrData> crDatas = f.get();
				crDatas.forEach(c -> {
					String provinceName = c.getProvinceName();
					List<CrData> cds = dataMap.get(provinceName);
					if (null == cds) {
						cds = new ArrayList<>();
						dataMap.put(provinceName, cds);
					}
					cds.add(c);
				});
			} catch (Exception e) {
				e.printStackTrace();
			}
		});
	}

	public static void doWithList(int threadNum, int every, String writePath, ExecutorService exe,
			List<CrData> dataList, LinkedBlockingQueue<CrData> queue) {
		List<List<CrData>> all = new ArrayList<>();
		List<Future<List<CrData>>> fs = new ArrayList<>();
		for (int i = 0; i < threadNum; i++) {
			List<CrData> tList = new ArrayList<CrData>();
			for (int j = 0; j < every; j++) {
				CrData crData = queue.poll();
				if (null == crData) {
					break;
				}
				tList.add(crData);
			}
			all.add(tList);
		}
		for (int i = 0; i < threadNum; i++) {
			MyCallable myCallable = new MyCallable(all.get(i), writePath);
			Future<List<CrData>> future = exe.submit(myCallable);
			fs.add(future);
		}
		fs.forEach(f -> {
			try {
				List<CrData> crDatas = f.get();
				dataList.addAll(crDatas);
			} catch (Exception e) {
				e.printStackTrace();
			}
		});
	}

	private static Set<String> zxsSet = new HashSet<>(Arrays.asList("北京", "天津", "上海", "重庆"));

	public static class MyCallable implements Callable<List<CrData>> {

		private final List<CrData> list;
		private final String writePath;

		public MyCallable(List<CrData> list, String writePath) {
			this.list = list;
			this.writePath = writePath;
		}

		@Override
		public List<CrData> call() throws Exception {
			for (CrData crData : list) {
				// 如果包含地市，一定包含省份
				if ("是".equals(crData.getIsContainCityKey())) {
					crData.setIsContainProKey("是");
				}
				// 逻辑口检查和非业务口检查只有有一个是，前面项置为\
				if ("是".equals(crData.getLogicalCheck()) || "是".equals(crData.getNotBusPortCheck())) {
					crData.setIsContainProKey("\\");
					crData.setIsContainCityKey("\\");
					crData.setIsContainNetBusKey("\\");
					crData.setIsContainDeviceKey("\\");
					crData.setIsContainBandwidthKey("\\");
					crData.setIsContainPortKey("\\");
				}
				crData.reloadCompliance();
				if (!crData.isCompliance()) {
					if (crData.getDeviceName().endsWith("163") && crData.getDeviceIp().startsWith("202.97.3")) {
						crData.setIsCompliance("合规");
						crData.setRemark("骨干设备下联端口");
					}
					if (zxsSet.contains(crData.getProvinceName())) {
						crData.setIsContainProKey("\\");
						crData.setIsContainCityKey("\\");
						crData.setRemark("直辖市下联端口暂不核查省份地市关键字");
						crData.reloadCompliance();
						if (!crData.isCompliance()) {
							if ("是".equals(crData.getBusPortCheck()) && crData.isBusUserCompliance()) {
								if (crData.getCircuitProperties().contains("下联")
										|| crData.getCircuitProperties().contains("自用口")) {
									crData.setIsCompliance("合规");
									crData.setRemark("直辖市下联端口暂不核查省份地市关键字，下联平台端口，无法获取对端设备端口信息，暂定合规");
								}
							} else if ("是".equals(crData.getUserPortCheck()) && crData.isBusUserCompliance()) {
								if (crData.getCircuitProperties().contains("下联")
										|| crData.getCircuitProperties().contains("自用口")) {
									crData.setIsCompliance("合规");
									crData.setRemark("直辖市下联端口暂不核查省份地市关键字，下联用户端口，无法获取对端设备端口信息，暂定合规");
								}
							}
						}
					} else {
						if ("是".equals(crData.getBusPortCheck()) && crData.isBusUserCompliance()) {
							crData.setIsCompliance("合规");
							crData.setRemark("下联平台端口，无法获取对端设备端口信息，暂定合规");
						} else if ("是".equals(crData.getUserPortCheck()) && crData.isBusUserCompliance()) {
							crData.setIsCompliance("合规");
							crData.setRemark("下联用户端口，无法获取对端设备端口信息，暂定合规");
						}
					}
				}

			}
			String filePath = writePath + Thread.currentThread().getId() + ".xlsx";
			write(list, filePath);
			File file = new File(filePath);
			list.clear();
			EasyExcel.read(file, CrData.class, new ReadListener<CrData>() {

				@Override
				public void invoke(CrData data, AnalysisContext context) {
					list.add(data);
				}

				@Override
				public void doAfterAllAnalysed(AnalysisContext context) {

				}
			}).sheet(0).doRead();
			file.delete();
			return list;
		}
	}

	public static void main(String[] args) {
		long a = System.currentTimeMillis();
		((LoggerContext) LoggerFactory.getILoggerFactory()).getLoggerList()
				.forEach(logger -> logger.setLevel(Level.INFO));
		int threadNum = 5;
		ExecutorService exe = Executors.newFixedThreadPool(threadNum);
		List<CrData> dataList = new ArrayList<>();
		Map<String, List<CrData>> dataMap = new LinkedHashMap<String, List<CrData>>();
		String filePath = "C:\\Users\\Administrator\\Desktop\\CR\\origin.xlsx";
		String writePath = "C:\\Users\\Administrator\\Desktop\\CR\\new\\";
		AtomicInteger no = new AtomicInteger();
		File writeFile = new File(writePath);
		if (!writeFile.exists()) {
			writeFile.mkdirs();
		}
		LinkedBlockingQueue<CrData> queue = new LinkedBlockingQueue<>();

		// Callable<List<CrData>> callable = new Callable<List<CrData>>() {
		//
		// @Override
		// public List<CrData> call() throws Exception {
		// List<CrData> list = new ArrayList<>();
		// for (int i = 0; i < 50; i++) {
		// CrData crData = queue.poll();
		// if (crData == null) {
		// break;
		// }
		// // 如果包含地市，一定包含省份
		// if ("是".equals(crData.getIsContainCityKey())) {
		// crData.setIsContainProKey("是");
		// }
		// // 逻辑口检查和非业务口检查只有有一个是，前面项置为\
		// if ("是".equals(crData.getLogicalCheck()) ||
		// "是".equals(crData.getNotBusPortCheck())) {
		// crData.setIsContainProKey("\\");
		// crData.setIsContainCityKey("\\");
		// crData.setIsContainNetBusKey("\\");
		// crData.setIsContainDeviceKey("\\");
		// crData.setIsContainBandwidthKey("\\");
		// crData.setIsContainPortKey("\\");
		// }
		//
		// if (crData.getDeviceName().endsWith("163") &&
		// crData.getDeviceIp().startsWith("202.97.3")) {
		// crData.setIsCompliance("合规");
		// crData.setRemark("骨干设备下联端口");
		// }
		// if (zxsSet.contains(crData.getPortName())) {
		// if ("不合规".equals(crData.getIsCompliance())) {
		// crData.setIsContainProKey("\\");
		// crData.setIsContainCityKey("\\");
		// if ("是".equals(crData.getBusPortCheck()) &&!(
		// !"是".equals(crData.getIsUpDownLink()) &&
		// !"是".equals(crData.getIsContainProKey()) &&
		// !"是".equals(crData.getIsContainCityKey())
		// )
		// ) {
		// if (crData.getCircuitProperties().contains("下联")||
		// crData.getCircuitProperties().contains("自用口")
		// ) {
		// crData.setIsCompliance("合规");
		// crData.setRemark("直辖市下联端口暂不核查省份地市关键字，下联平台端口，无法获取对端设备端口信息，暂定合规");
		// }
		// }
		// else if ("是".equals(crData.getUserPortCheck()) &&!(
		// !"是".equals(crData.getIsUpDownLink()) &&
		// !"是".equals(crData.getIsContainProKey()) &&
		// !"是".equals(crData.getIsContainCityKey())
		// )
		// ) {
		// if (crData.getCircuitProperties().contains("下联")||
		// crData.getCircuitProperties().contains("自用口")
		// ) {
		// crData.setIsCompliance("合规");
		// crData.setRemark("直辖市下联端口暂不核查省份地市关键字，下联用户端口，无法获取对端设备端口信息，暂定合规");
		// }
		// }
		// }
		// } else {
		// if ("是".equals(crData.getBusPortCheck()) &&
		// "是".equals(crData.getIsUpDownLink()) &&
		// "是".equals(crData.getIsContainProKey()) &&
		// "是".equals(crData.getIsContainCityKey())) {
		// if ("不合规".equals(crData.getIsCompliance())) {
		// crData.setIsCompliance("合规");
		// crData.setRemark("下联平台端口，无法获取对端设备端口信息，暂定合规");
		// }
		// }
		// else if ("是".equals(crData.getUserPortCheck()) &&
		// "是".equals(crData.getIsUpDownLink()) &&
		// "是".equals(crData.getIsContainProKey()) &&
		// "是".equals(crData.getIsContainCityKey())) {
		// if ("不合规".equals(crData.getIsCompliance())) {
		// crData.setIsCompliance("合规");
		// crData.setRemark("下联用户端口，无法获取对端设备端口信息，暂定合规");
		// }
		// }
		// }
		// list.add(crData);
		// }
		// String filePath = writePath + Thread.currentThread().getId() + ".xlsx";
		// write(list, filePath);
		// File file = new File(filePath);
		// list.clear();
		// EasyExcel.read(file, CrData.class, new ReadListener<CrData>() {
		//
		// @Override
		// public void invoke(CrData data, AnalysisContext context) {
		// list.add(data);
		// }
		//
		// @Override
		// public void doAfterAllAnalysed(AnalysisContext context) {
		//
		// }
		// }).sheet(0).doRead();
		// file.delete();
		// return list;
		// }
		// };
		int every = 50;
		EasyExcel.read(filePath, CrReadData.class, new ReadListener<CrReadData>() {
			@Override
			public void invoke(CrReadData data, AnalysisContext context) {
				int n = no.incrementAndGet();
				if (n % 10000 == 0) {
					System.out.println("done:" + n);
					dataMap.forEach((k, v) -> {
						System.out.println(k + "-->" + v.size());
					});
				}
				if (StringUtils.isEmpty(data.getProvinceName())) {
					return;
				}
				queue.add(JSON.parseObject(JSON.toJSONString(data), CrData.class));
				if (queue.size() >= every * threadNum) {
					doWithList(threadNum, every, writePath, exe, dataMap, queue);
				}
			}

			@Override
			public void doAfterAllAnalysed(AnalysisContext context) {

			}
		}).sheet(0).doRead();
		if (queue.size() > 0) {
			doWithList(threadNum, every, writePath, exe, dataMap, queue);
		}
		System.out.println("done:" + dataList.size() + " cost:" + (System.currentTimeMillis() - a));
		exe.shutdown();
		dataMap.forEach((k, v) -> {
			EasyExcel.write("C:\\Users\\Administrator\\Desktop\\CR\\" + k + ".xlsx", CrData.class).sheet("Sheet1")
					.doWrite(v);
		});
	}

	public static void write(List<CrData> dataList, String writePath) {
		ExcelWriter excelWriter = EasyExcel.write(writePath, CrData.class)
				.registerWriteHandler(new CrCellWriteHandler()).build();
		// 创建Sheet对象
		WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").build();
		excelWriter.write(dataList, writeSheet);

		Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		evaluator.evaluateAll(); // 全表公式计算

		excelWriter.finish();
	}
}